package com.linxi.handy.component.view;

import com.linxi.handy.common.Constants;
import com.linxi.handy.component.control.HomeControl;

import javax.swing.*;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据表视图
 *
 * @author user
 */
public class Tableview extends JPanel {

    /**
     * 连接数据库所用的变量，需要赋值
     */
    public static Connection conn;
    /**
     * 空值表示，本函数所用的空值表示，在表格显示的时候，和""空字符串区分开来，在显示和增加，修改，查询过程中都会将该字符串认为空值处理
     */
    public static String control = "Null(1)";
    String databaseName;
    String tableName;
    /**
     * 表中列的个数
     */
    int tableNum;
    /**
     * 表中列的名字
     */
    String[] tableColName;
    /**
     * 保存被修改前的旧值，用以确认唯一的修改对象。注意，每张表都对应一个旧值表，更新内容时候会被同步刷新。
     */
    Object[][] oldValue;
    /**
     * 构造界面所用的组件
     */
    JPanel panel = new JPanel();
    JScrollPane scrollPane = new JScrollPane();
    /**
     * 此处三个变量在filltable()函数中初始化
     */
    DefaultTableModel tableModel;
    JTable table;
    /**
     * 输入面板
     */
    JTable table2;

    JPanel buttOnPanel = new JPanel();
    JButton add = new JButton("增加");
    JButton delete = new JButton("删除");
    JButton select = new JButton("查找");
    JButton refresh = new JButton("刷新");

    public Tableview(String databaseName, String tableName) {
        this.databaseName = databaseName;
        this.tableName = tableName;
        if (conn == null) {
            return;
        }

        initTable();
        table.setFont(Constants.DEFAULT_FONT_13);
        table2.setFont(Constants.DEFAULT_FONT_13);
        scrollPane.setViewportView(table);
        panel.setLayout(new BorderLayout());
        panel.add(scrollPane);
        panel.add(table2, BorderLayout.SOUTH);

        add.addActionListener(new ButtonProcessListener());
        delete.addActionListener(new ButtonProcessListener());
        select.addActionListener(new ButtonProcessListener());
        refresh.addActionListener(new ButtonProcessListener());
        buttOnPanel.add(add);
        buttOnPanel.add(delete);
        buttOnPanel.add(select);
        buttOnPanel.add(refresh);

        this.setLayout(new BorderLayout());
        this.add(panel);
        this.add(buttOnPanel, BorderLayout.SOUTH);
        //设置一些颜色和其他属性 new Color(245,245,245));
        add.setBackground(Color.WHITE);
        delete.setBackground(Color.WHITE);
        select.setBackground(Color.WHITE);
        refresh.setBackground(Color.WHITE);
    }

    public static void executeSql(String sql) {
        int i = 0;
        String error = null;
        try {
            Statement stmt = conn.createStatement();
            i = stmt.executeUpdate(sql);
            stmt.close();
        } catch (SQLException e1) {
            error = e1.getMessage();
            e1.printStackTrace();
        }
        HomeControl.fdi.setText(sql, i, error);
    }

    /**
     * 初始化表格
     */
    void initTable() {
        try {
            Statement stmt = conn.createStatement();
            String sql = "select column_name from information_schema.columns where table_name='" + tableName + "'and table_schema ='" + databaseName + "'";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                tableNum++;
            }
            //存放当前表的列名
            tableColName = new String[tableNum];
            table2 = new JTable(1, tableNum);
            //最多为100行，构造一个最大数组，来放旧值
            oldValue = new Object[100][tableNum];

            //因为rs.next()统计个数，重新初始化
            rs = stmt.executeQuery(sql);
            for (int j = 0; j < tableNum; j++) {
                rs.next();
                tableColName[j] = new String(rs.getString("column_name"));
            }

            tableModel = new DefaultTableModel(null, tableColName);
            table = new JTable(tableModel) {
                private static final long serialVersionUID = 1L;

                //Implement table cell tool tips.增加行提示
                @Override
                public String getToolTipText(MouseEvent e) {
                    String tip = null;
                    Point p = e.getPoint();
                    int rowIndex = rowAtPoint(p);
                    int colIndex = columnAtPoint(p);
                    tip = (String) this.getValueAt(rowIndex, colIndex);
                    return tip;
                }

                //Implement table header tool tips. 增加表头提示
                @Override
                protected JTableHeader createDefaultTableHeader() {
                    return new JTableHeader(columnModel) {
                        private static final long serialVersionUID = 1L;

                        @Override
                        public String getToolTipText(MouseEvent e) {
                            Point p = e.getPoint();
                            int index = columnModel.getColumnIndexAtX(p.x);
                            int realIndex = columnModel.getColumn(index).getModelIndex();
                            return tableColName[realIndex];
                        }
                    };
                }
            };
            //设置一些表的属性 禁止拖动列头排序，不然会造成修改错误
            table.getTableHeader().setReorderingAllowed(false);
            table2.setSelectionBackground(new Color(100, 200, 200));
            //截至目前代码，表的列名已经初始化完毕，列数在tablecnum中
            //开始向表格填充数据
            String sql2 = "select * from " + databaseName + "." + tableName + " limit 0,100";
            rs = stmt.executeQuery(sql2);
            getRowValue(stmt, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        tableModel.addTableModelListener(new TableProcessListener());
    }

    private void getRowValue(Statement stmt, ResultSet rs) throws SQLException {
        Object[] rowValue = new Object[tableNum];

        int i = 0;
        while (rs.next()) {
            for (int j = 0; j < tableNum; j++) {
                rowValue[j] = rs.getString(tableColName[j]);
                if (rowValue[j] == null) {
                    rowValue[j] = control;
                }

                //存到缓冲表中
                oldValue[i][j] = rowValue[j];
            }
            i++;
            tableModel.addRow(rowValue);
        }
        rs.close();
        stmt.close();
    }

    /**
     * 单表刷新 刷新表格
     */
    public void refreshTable() {
        //不能试图在这个函数里面取消编辑状态，会触发修改任务，造成程序失败，应该在某个任务之前取消编辑状态
        try {
            Statement stmt = conn.createStatement();
            //先清空
            tableModel.setRowCount(0);

            String sql = "select * from " + databaseName + "." + tableName + " limit 0,100";
            ResultSet rs = stmt.executeQuery(sql);
            getRowValue(stmt, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    class ButtonProcessListener implements ActionListener {
        @Override
        public void actionPerformed(ActionEvent e) {
            //增加功能
            if (e.getSource() == add) {
                new BatchAddDialog();
            }
            //删除功能
            if (e.getSource() == delete) {
                int hang[] = table.getSelectedRows();
                if (hang.length <= 0) {
                    return;
                }

                int n = JOptionPane.showConfirmDialog(null, "确认删除吗?", "删除", JOptionPane.YES_NO_OPTION);
                if (n != JOptionPane.YES_OPTION) {
                    return;
                }

                if (table.isEditing()) {
                    table.getCellEditor().stopCellEditing();//如果要删除条目的表正在编辑状态，就停止编辑状态
                }
                //批量删除
                for (int which = 0; which < hang.length; which++) {
                    String sql = "delete from " + databaseName + "." + tableName + "  where ";
                    Object value = null;
                    for (int j = 0; j < tableNum; j++) {
                        if (value != null) {
                            sql = sql + " and ";
                        }
                        value = table.getValueAt(hang[which], j);
                        if (control.equals(value)) {
                            sql = sql + table.getColumnName(j) + " is null ";
                        } else {
                            sql = sql + table.getColumnName(j) + " = '" + table.getValueAt(hang[which], j) + "' ";
                        }
                    }
                    //System.out.println(sql);//检查sql语句是否正确
                    executeSql(sql);
                }
                refreshTable();
            }
            //查找功能
            if (e.getSource() == select) {
                try {
                    if (table.isEditing()) {
                        //如果要增加条目的表正在编辑状态，就停止编辑状态
                        table.getCellEditor().stopCellEditing();
                    }
                    if (table2.isEditing()) {
                        //如果要增加条目的表正在编辑状态，就停止编辑状态
                        table2.getCellEditor().stopCellEditing();
                    }

                    tableModel.setRowCount(0);//先清空

                    String sql = "select * from " + databaseName + "." + tableName;
                    String value = null;
                    for (int j = 0; j < tableNum; j++) {
                        //不对空值或空字符串进行查找
                        if (table2.getValueAt(0, j) != null && !table2.getValueAt(0, j).equals("")) {
                            if (value == null) {
                                sql = sql + " where ";
                            } else {
                                sql = sql + " and ";
                            }
                            value = (String) table2.getValueAt(0, j);
                            if (control.equals(value)) {
                                sql = sql + table.getColumnName(j) + " is null ";
                            } else {
                                sql = sql + table.getColumnName(j) + " like '%" + value + "%' ";
                            }
                        }
                    }
                    sql = sql + " limit 0,100";
                    //System.out.println(sql);//检查sql语句是否正确
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery(sql);
                    getRowValue(stmt, rs);
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            //刷新功能
            if (e.getSource() == refresh) {
                if (table.isEditing()) {
                    //如果要刷新的表正在编辑状态，就停止编辑状态
                    table.getCellEditor().stopCellEditing();
                }
                if (table2.isEditing()) {
                    //如果要刷新的表正在编辑状态，就停止编辑状态
                    table2.getCellEditor().stopCellEditing();
                }
                table.clearSelection();
                table2.clearSelection();
                refreshTable();
            }
        }

    }

    /**
     * 增加功能的弹窗
     */
    class BatchAddDialog extends JDialog {
        private static final long serialVersionUID = 1L;

        JLabel l = new JLabel();

        JScrollPane sp = new JScrollPane();
        Object[][] data = new Object[20][tableNum];
        DefaultTableModel addModel = new DefaultTableModel(data, tableColName);
        JTable add = new JTable(addModel);

        JPanel p = new JPanel();
        JButton b1 = new JButton("全   选");
        JButton b2 = new JButton("增   加");
        JButton b3 = new JButton("扩展十行");
        JLabel b4 = new JLabel("<HTML><U>重置...</U></HTML>");

        BatchAddDialog() {
            super(HomeControl.base, false);

            l.setText("选择要增加的行,可多选");
            sp.setViewportView(add);
            b1.addActionListener(new HintProcessListener());
            b2.addActionListener(new HintProcessListener());
            b3.addActionListener(new HintProcessListener());
            b4.addMouseListener(new MouseAdapter() {
                @Override
                public void mouseClicked(MouseEvent arg0) {
                    if (add.isEditing()) {
                        //如果要刷新的表正在编辑状态，就停止编辑状态
                        add.getCellEditor().stopCellEditing();
                    }
                    add.clearSelection();
                    addModel.setRowCount(0);
                    addModel.setRowCount(20);
                }
            });
            p.add(b2);
            p.add(b1);
            p.add(b3);
            p.add(b4);
            this.setLayout(new BorderLayout());
            this.add(l, BorderLayout.NORTH);
            this.add(sp);
            this.add(p, BorderLayout.SOUTH);

            //设置一些属性
            b1.setBackground(Color.WHITE);
            b2.setBackground(Color.WHITE);
            b3.setBackground(Color.WHITE);
            //禁止拖动列头排序，不然会造成修改错误
            add.getTableHeader().setReorderingAllowed(false);

            this.setSize(800, 500);
            //将窗体设置在屏幕中间
            Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
            Dimension size = this.getSize();
            int x = (screenSize.width - size.width) / 2;
            int y = (screenSize.height - size.height) / 2;
            this.setLocation(x + 50, y + 100);
            this.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
            this.setModal(true);
            this.setVisible(true);
        }

        class HintProcessListener implements ActionListener {
            @Override
            public void actionPerformed(ActionEvent e) {
                if (e.getSource() == b1) {
                    add.selectAll();
                }
                if (e.getSource() == b2) {
                    int hang[] = add.getSelectedRows();
                    if (hang.length <= 0) {
                        return;
                    }
                    if (hang.length == 1) {
                        int n = JOptionPane.showConfirmDialog(null, "只选择了一行，继续执行吗？", "提示", JOptionPane.YES_NO_OPTION);
                        if (n != JOptionPane.YES_OPTION) {
                            return;
                        }
                    }
                    if (table.isEditing()) {
                        //如果要增加条目的表正在编辑状态，就停止编辑状态
                        table.getCellEditor().stopCellEditing();
                    }
                    if (add.isEditing()) {
                        //如果要增加条目的表正在编辑状态，就停止编辑状态
                        add.getCellEditor().stopCellEditing();
                    }
                    //批量增加
                    String sql = "insert into " + databaseName + "." + tableName + "  values(";
                    //标记第几次
                    int cishu1 = 0;
                    for (int which = 0; which < hang.length; which++) {
                        //加逗号
                        if (cishu1 != 0) {
                            sql = sql + ",(";
                        }
                        Object value = null;
                        int cishu2 = 0;//标记第几次
                        for (int j = 0; j < tableNum; j++) {
                            if (cishu2 != 0) {
                                //加逗号
                                sql = sql + " , ";
                            }
                            value = add.getValueAt(hang[which], j);
                            if (control.equals(value)) {
                                //区别null空值
                                sql = sql + "null";
                            } else {
                                if (value == null) {
                                    //把输入框默认的null(空值)作为''(空白字符串)
                                    sql = sql + "''";
                                } else {
                                    sql = sql + "'" + value + "'";
                                }
                            }
                            cishu2++;
                        }
                        sql = sql + ")";
                        cishu1++;
                        //检查sql语句是否正确
                        //System.out.println(sql);
                    }
                    Tableview.executeSql(sql);
                    refreshTable();
                }
                if (e.getSource() == b3) {
                    int i = addModel.getRowCount();
                    addModel.setRowCount(i + 10);
                }
            }
        }
    }

    /**
     * 修改功能
     *
     * @author user
     */
    class TableProcessListener implements TableModelListener {

        @Override
        public void tableChanged(TableModelEvent e) {
            if (e.getType() == TableModelEvent.UPDATE) {
                //行
                int hang = table.getSelectedRow();
                //列
                int lie = table.getSelectedColumn();
                //增加这个判断是必要的
                if (hang < 0) {
                    return;
                }
                String sql = null;
                int i = 0;
                String error = null;
                try {
                    Statement stmt = conn.createStatement();
                    Object value;
                    value = table.getValueAt(hang, lie);
                    if (value.equals(oldValue[hang][lie])) {
                        return;
                    }

                    if (control.equals(value)) {
                        sql = "update " + databaseName + "." + tableName + " set " + tableColName[lie] + " = null where ";
                    } else {
                        sql = "update " + databaseName + "." + tableName + " set " + tableColName[lie] + " = '" + value + "' where ";
                    }

                    value = null;//标记第几次,此处可以用null来判别，而增加功能中不可以
                    for (int j = 0; j < tableNum; j++) {
                        if (value != null) {
                            sql = sql + " and ";
                        }
                        value = table.getValueAt(hang, j);
                        if (j == lie) {
                            value = oldValue[hang][lie];
                        }
                        if (control.equals(value)) {
                            sql = sql + table.getColumnName(j) + " is null ";
                        } else {
                            sql = sql + table.getColumnName(j) + " = '" + value + "' ";
                        }
                    }
                    //System.out.println(sql);//检查sql语句是否正确
                    i = stmt.executeUpdate(sql);
                    //此处之后语句，若stmt.executeUpdate(sql);抛出异常则执行不到
                    stmt.close();
                } catch (SQLException e1) {
                    error = e1.getMessage();
                    e1.printStackTrace();
                }

                HomeControl.fdi.setText(sql, i, error);
                refreshTable();//刷新当前更改的表，防止修改失败
            }
        }
    }
}



